/*

    USAGE NOTES: Constructor requires a string that is the database name

    getTable method returns a JTable or throws an exception
    STRING parameters required are:
        getTable( USERNAME, PASSWORD, YOUR_PREMADE_QUERY )

    Sample Usage:

DBQTable dbq = new DBQTable(database);
JScrollPane scroll;
Container c = getContentPane();

try{
	JTable table = dbq.getTable(username,password,"Select * from faculty where deptid=15");
	scroll = new JScrollPane(table);
	c.add(scroll);
	c.validate();      //necessary to refresh the UI after adding the table;
}catch (Exception x){
	x.printStackTrace();
}

*/

import java.sql.*;
import javax.swing.JTable;
import java.util.Vector;
import java.awt.Dimension;

class DBQTable {

    static final String driver = "COM.ibm.db2.jdbc.app.DB2Driver";
    String url;


    DBQTable (String dbname){
            //NOTICE: In URL...3rd segment is the DATABASE NAME
        url = "jdbc:db2:" + dbname;
    }

    public JTable getTable(String user, String pass, String strQuery) throws Exception{

        Connection connection;
        Statement statement;
        ResultSet resultSet;
        ResultSetMetaData metaData;
        JTable returnTable;
        Vector columnNames = new Vector();
        Vector thisRow = new Vector();
        Vector rows = new Vector();
        int numberOfColumns=0;


        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url,user,pass);

            statement = connection.createStatement();

            resultSet = statement.executeQuery(strQuery);
            //System.out.println(resultSet.toString());

            metaData = resultSet.getMetaData();
            numberOfColumns = metaData.getColumnCount();

            //load up Column names
            for(int i = 1; i <= metaData.getColumnCount(); i++){
                columnNames.addElement(metaData.getColumnName(i));
            }


            //get data
            while (resultSet.next()){
                for (int j=1;j<=numberOfColumns;j++){
                    thisRow.addElement(resultSet.getObject(j));
                    //System.out.println(resultSet.getObject(j).toString());
                }
                rows.addElement(thisRow);
                thisRow = new Vector();
            }


            returnTable = new JTable(rows, columnNames);
            returnTable.setPreferredScrollableViewportSize(
                    new Dimension(400, 400));


            statement.close();
            connection.close();

            return returnTable;
        } catch (ClassNotFoundException dunno){
            System.out.println("Cannot find driver class");
            dunno.printStackTrace();
            throw new Exception();
        } catch (SQLException sql){
            System.out.println("SQL error");
            //sql.printStackTrace();
            throw new Exception();
        }

    }

    public Vector getVector(String user, String pass, String strQuery) throws Exception{

        Connection connection;
        Statement statement;
        ResultSet resultSet;
        ResultSetMetaData metaData;
        //Vector columnNames = new Vector();
        Vector rows = new Vector();
        int numberOfColumns=0;


        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url,user,pass);

            statement = connection.createStatement();

            resultSet = statement.executeQuery(strQuery);
            //System.out.println(resultSet.toString());

            metaData = resultSet.getMetaData();
            numberOfColumns = metaData.getColumnCount();

            //load up Column names
            //for(int i = 1; i <= metaData.getColumnCount(); i++){
            //    columnNames.addElement(metaData.getColumnName(i));
            //}


            //get data
            while (resultSet.next()){

                rows.addElement(resultSet.getObject(1));
                    //System.out.println(resultSet.getObject(j).toString());
            }


            statement.close();
            connection.close();

            return rows;
        } catch (ClassNotFoundException dunno){
            System.out.println("Cannot find driver class");
            dunno.printStackTrace();
            throw new Exception();
        } catch (SQLException sql){
            System.out.println("SQL error");
            sql.printStackTrace();
            throw new Exception();
        }

    }

    public int update(String user, String pass, String strQuery) {
//throws Exception
        Connection connection;
        Statement statement;
        int updateCount;

        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url,user,pass);

            statement = connection.createStatement();

            updateCount = statement.executeUpdate(strQuery);
            //System.out.println(resultSet.toString());

            statement.close();
            connection.close();

            return updateCount;
        } catch (ClassNotFoundException dunno){
            System.out.println("Cannot find driver class");
            dunno.printStackTrace();
            //throw new Exception();
            return 0;
        } catch (SQLException sql){
            System.out.println("SQL error");
            sql.printStackTrace();
            //throw new Exception();
            return 0;
        }

    }


}
